package jjanga.sqlite;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentResolver;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.net.Uri;
import android.provider.MediaStore;
import android.provider.MediaStore.Files;

public class FmFileDatabase {
	
	public class DocType{
    	static public final int WORD_2003 = 1;
    	static public final int WORD_2007 = 2;
    	static public final int EXCEL_2003 = 3;
    	static public final int EXCEL_2007 = 4;
    	static public final int PPT_2003 = 5;
    	static public final int PPT_2007 = 6;
    }
    
    public class FileFormat{
    	static public final int FOLDER = 12289;
    	static public final int WORD_2003 = 47747;
    	static public final int WORD_2007 = 12288;
    	static public final int EXCEL_2003 = 47749;
    	static public final int EXCEL_2007 = 12288;
    	static public final int PPT_2003 = 47750;
    	static public final int PPT_2007 = 12288;
    }    
    
    public class MimeType{
    	static public final String WORD_2003 = "application/msword";
    	static public final String WORD_2007 = "application/vnd.openxmlformats-officedocument.wordprocessingml.document";
    	static public final String EXCEL_2003 = "application/vnd.ms-excel";
    	static public final String EXCEL_2007 = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    	static public final String PPT_2003 = "application/vnd.ms-powerpoint";
    	static public final String PPT_2007 = "application/vnd.openxmlformats-officedocument.presentationml.presentation";
    }
    
    public class Operation{
    	static public final int COPY = 0;
    	static public final int MOVE = 1;
    	static public final int RENAME = 2;
    }
    
    final String[] FileProjection = new String[]{
	    	Files.FileColumns._ID,  //INTEGER
	    	Files.FileColumns.DATA, //TEXT
	    	Files.FileColumns.SIZE, //INTEGER
	    	"format",               //INTEGER
	    	Files.FileColumns.DATE_ADDED, //INTEGER
	    	Files.FileColumns.DATE_MODIFIED, //INTEGER
	    	Files.FileColumns.MIME_TYPE, //TEXT
	    	Files.FileColumns.TITLE, //TEXT
//	    	"description",//TEXT
//	    	"picasa_id", //TEXT
//	    	"orientation", //INTEGER
//	    	"latitude", //DOUBLE
//	    	"longitude", //DOUBLE
//	    	"datetaken", //INTEGER
//	    	"mini_thumb_magic", //INTEGER
	    	"bucket_id", //TEXT	    	
	    	"bucket_display_name",  //TEXT
//	    	"isprivate", //INTEGER
//	    	"title_key", //TEXT
//	    	"artist_id", //INTEGER
//	    	"album_id", //INTEGER
//	    	"composer", //TEXT
//	    	"trac", //INTEGER
//	    	"year", //INTEGER
//	    	"is_ringtone", //INTEGER
//	    	"is_music", //INTEGER
//	    	"is_alarm", //INTEGER
//	    	"is_notification", //INTEGER
	    	Files.FileColumns.MEDIA_TYPE,  //INTEGER
	    	"parent", //INTEGER
	};
    
    class DBFileItem{
	    long nID = 0;	    
	    long nSize = 0;
	    int nFormat = 0;
	    int nMediaType = 0;
	    long nDateAdded = 0;
	    long nDateModified = 0;
	    long nParent = 0;
	    String strData = null;
	    String strMimeType = null;
	    String strTitle = null;
	    String strBucketID = null;
	    String strBucketDisplayName = null;	    
    }
    
    Context m_oContext;
    ContentResolver m_oCr;
    
    public FmFileDatabase(Context a_oContext){
    	m_oContext = a_oContext;
    	m_oCr = m_oContext.getContentResolver();
    }
    
    public boolean UpdateQuery(ContentValues a_oValues, String a_strWhere){
    	int nResult = m_oCr.update(MediaStore.Files.getContentUri("external"), a_oValues, a_strWhere, null);
    	if(nResult < 0)
    		return false;
    	else
    		return true;
    }
    
    public ArrayList<DBFileItem> Query(String where){
    	ArrayList<DBFileItem> oDBFiles = new ArrayList<DBFileItem>();			
		Cursor oCursor = null;
		oCursor = m_oCr.query(MediaStore.Files.getContentUri("external"), FileProjection, where, null, null);
		DBFileItem oFileItem;
		while(oCursor.moveToNext()){
			oFileItem = ConvertData(oCursor);
			oDBFiles.add(oFileItem);
		}				
		if(oCursor != null)
			oCursor.close();
		return oDBFiles;
    }
    
    public ArrayList<DBFileItem> GetFileList(String a_strFolderPath){
    	String strFolderPath = RemoveSlush(a_strFolderPath);
    	ArrayList<DBFileItem> oDBFiles = new ArrayList<DBFileItem>();
    	File oFolder = new File(strFolderPath);
    	if(oFolder.exists() == true){
    		try {
				strFolderPath = oFolder.getCanonicalPath();
			} catch (IOException e) {
				e.printStackTrace();
				return null;
			}
			Cursor oCursor = null;
			oCursor = m_oCr.query(MediaStore.Files.getContentUri("external"), FileProjection, "_data='"+strFolderPath+"'", null, null);
			if(oCursor != null && oCursor.moveToFirst() && oCursor.getCount() == 1){
				DBFileItem oFileItem = ConvertData(oCursor);
				long nId = oFileItem.nID;
				oCursor = m_oCr.query(MediaStore.Files.getContentUri("external"), FileProjection, "parent='"+Long.toString(nId)+"'", null, null);
				while(oCursor.moveToNext()){
					oFileItem = ConvertData(oCursor);
					oDBFiles.add(oFileItem);
				}				
			}
			if(oCursor != null)
				oCursor.close();
			return oDBFiles;
    	}else
    		return null;
    }
    
    public boolean MoveFileDatabase(String a_strSrcFullPath, String a_strDestFullPath){
    	String strSrcFullPath = RemoveSlush(a_strSrcFullPath);
    	String strDestFullPath = RemoveSlush(a_strDestFullPath);
    	boolean bResult = false;
    	bResult = CopyFileDatabase(strSrcFullPath, strDestFullPath);
    	if(bResult == true)
    		return DeleteFileDatabase(strSrcFullPath);
    	else    		
    		return true;
    }

    public boolean CopyFileDatabase(String a_strSrcFullPath, String a_strDestFullPath){
    	String strSrcCanonicalFilePath = RemoveSlush(a_strSrcFullPath);
    	String strDestCanonicalPath = RemoveSlush(a_strDestFullPath);
    	ContentValues values = new ContentValues();
    	DBFileItem oFileItem = new DBFileItem();
    	File oSrcFile = new File(a_strSrcFullPath);
    	File oDestFile = new File(a_strDestFullPath);
    	
    	if(oSrcFile.exists() == true && oDestFile.exists() == true){
			Cursor oCursor = null;
			try {
				strSrcCanonicalFilePath = oSrcFile.getCanonicalPath();
				strDestCanonicalPath = oDestFile.getCanonicalPath();
			} catch (IOException e) {
				e.printStackTrace();
			}

			oCursor = m_oCr.query(MediaStore.Files.getContentUri("external"), FileProjection, "_data='"+strSrcCanonicalFilePath+"'", null, null);
			
			if(oCursor != null && oCursor.moveToFirst() && oCursor.getCount() == 1){
				oFileItem = ConvertData(oCursor);
												
				values.put(FileProjection[1], strDestCanonicalPath);
				values.put(FileProjection[2], oDestFile.length());
				values.put(FileProjection[3], oFileItem.nFormat);
				values.put(FileProjection[6], oFileItem.strMimeType);
				values.put(FileProjection[7], oFileItem.strTitle);
				values.put(FileProjection[10], oFileItem.nMediaType);
			}
			if(oCursor != null)
				oCursor.close();
			m_oCr.insert(MediaStore.Files.getContentUri("external"), values);
    	}
    	return true;    	
    }
    
    public boolean InsertFolderDatabase(String a_strPath){
    	String strFilePath = RemoveSlush(a_strPath);
    	String CanonicalPath = null;    	
		File oFile = new File(strFilePath);
		if(oFile.exists() == false){
			boolean bResult = oFile.mkdirs();
			if(bResult == true){
				ContentValues values = new ContentValues();						
				try {
					CanonicalPath = oFile.getCanonicalPath();
				} catch (IOException e) {
					e.printStackTrace();
				}
				values.put(MediaStore.Files.FileColumns.DATA, CanonicalPath);
				values.put("format", FileFormat.FOLDER);
				m_oCr.insert(MediaStore.Files.getContentUri("external"), values);
			}			
			return true;
		}else			
			return false;
    }
    
    public boolean InsertFileDatabase(String a_strPath, int a_nType){
    	String strFilePath = RemoveSlush(a_strPath);
    	int nFormatType = 0;
    	String strMimeType = null;
    	
    	switch(a_nType){
    	case DocType.WORD_2003:
    		strMimeType = MimeType.WORD_2003;
    		nFormatType = FileFormat.WORD_2003;
    		break;
    	case DocType.WORD_2007:
    		strMimeType = MimeType.WORD_2007;
    		nFormatType = FileFormat.WORD_2007;
    		break;
    	case DocType.EXCEL_2003:
    		strMimeType = MimeType.EXCEL_2003;
    		nFormatType = FileFormat.EXCEL_2003;
    		break;
    	case DocType.EXCEL_2007:
    		strMimeType = MimeType.EXCEL_2007;
    		nFormatType = FileFormat.EXCEL_2007;
    		break;
    	case DocType.PPT_2003:
    		strMimeType = MimeType.PPT_2003;  
    		nFormatType = FileFormat.PPT_2003;
    		break;
    	case DocType.PPT_2007:
    		strMimeType = MimeType.PPT_2007;
    		nFormatType = FileFormat.PPT_2007;
    		break;
    	}
    	if(strFilePath == null)
    		return false;
    	
    	File oFile = new File(strFilePath);
    	if(oFile.exists() == true){
    		ContentValues values = new ContentValues();
    		String CanonicalPath = null;
    		try {
				CanonicalPath = oFile.getCanonicalPath();
			} catch (IOException e) {				
				e.printStackTrace();
			}
			values.put(MediaStore.Files.FileColumns.DATA, CanonicalPath);
			values.put(Files.FileColumns.SIZE, oFile.length());
			values.put(Files.FileColumns.DATE_MODIFIED, oFile.lastModified());
			values.put(Files.FileColumns.TITLE, removeExtension(oFile.getName()));
			values.put("format", nFormatType);
			values.put(Files.FileColumns.MIME_TYPE, strMimeType);
			
    	}else{
    		try {
				oFile.createNewFile();
			} catch (IOException e) {
				e.printStackTrace();
			}
			
    		ContentValues values = new ContentValues();
    		String CanonicalPath = null;
    		try {
				CanonicalPath = oFile.getCanonicalPath();
			} catch (IOException e) {				
				e.printStackTrace();
			}
			values.put(MediaStore.Files.FileColumns.DATA, CanonicalPath);
			values.put(Files.FileColumns.SIZE, oFile.length());
			values.put("format", nFormatType);
			values.put(Files.FileColumns.DATE_MODIFIED, oFile.lastModified());
			values.put(Files.FileColumns.MIME_TYPE, strMimeType);
			values.put(Files.FileColumns.TITLE, removeExtension(oFile.getName()));    		
    		return false;	
    	}
    	return true;
    }
    
    public boolean DeleteFileDatabase(String a_strPath){
    	String strFilePath = RemoveSlush(a_strPath);

//    	File oFile = new File(strFilePath);
//    	if(oFile.exists() == true){
//    		boolean bResult = oFile.delete();
//    		if(bResult == true){
    			Cursor oCursor = null;
    			int nDestID = 0;
    			String strDestPath = null;
//    			try {
//					strDestPath = oFile.getCanonicalPath();
//				} catch (IOException e) {
//					e.printStackTrace();
//				}			

				oCursor = m_oCr.query(MediaStore.Files.getContentUri("external"), null, "_data='"+strFilePath+"'", null, null);
    			if(oCursor != null && oCursor.moveToFirst() && oCursor.getCount() == 1){
    				nDestID = oCursor.getInt(0);
    			}
    			if(oCursor != null)
    				oCursor.close();
    			int nResult = -1;
    			nResult = m_oCr.delete(MediaStore.Files.getContentUri("external"), "_id='"+Integer.toString(nDestID)+"'", null);
    			if(nResult >= 0)
    				return true;
    			else
    				return false;
//    		}
//    	}
//    	return false;
    }
    
    public ArrayList<DBFileItem> GetDocFiles(){
    	String where = "_data LIKE '%.doc' OR _data LIKE '%.docx' AND format != 12289";
    	return GetDocuments(where);
    }
    
    public ArrayList<DBFileItem> GetSheetFiles(){
    	String where = "_data LIKE '%.xls%' OR _data LIKE '%.xlsx' AND format != 12289";    	
    	return GetDocuments(where);
    }
    
    public ArrayList<DBFileItem> GetSlideFiles(){
    	String where = "_data LIKE '%.ppt%' OR _data LIKE '%.pptx' AND format != 12289";    	
    	return GetDocuments(where);
    }
    
    public ArrayList<DBFileItem> GetDocuments(String a_strWhere){
    	String strWhere = RemoveSlush(a_strWhere);
    	DBFileItem oFileItem = new DBFileItem();
    	ArrayList<DBFileItem> oDBFiles = new ArrayList<FmFileDatabase.DBFileItem>();

    	Cursor oCursor = null;
		oCursor = m_oCr.query(MediaStore.Files.getContentUri("external"), FileProjection, strWhere, null, null);
		
		if(oCursor != null){
			while(oCursor.moveToNext()){
				oFileItem = ConvertData(oCursor);
				oDBFiles.add(oFileItem);
			}
		}
		if(oCursor != null)
			oCursor.close();
		
		return oDBFiles;
    }
    
    String removeExtension(String a_strFileName){
		String strFileName = a_strFileName;
		if(strFileName == null)
			return null;
		int nIndex = strFileName.lastIndexOf('.');
		if(nIndex == -1)
			return strFileName;
		
		return strFileName.substring(0, nIndex);
    }

	public DBFileItem ConvertData(Cursor a_oCursor) {
		DBFileItem oDbFileItem = new DBFileItem();
		
		oDbFileItem.nID = a_oCursor.getInt(0);
		oDbFileItem.strData = a_oCursor.getString(1);
		oDbFileItem.nSize = a_oCursor.getLong(2);
		oDbFileItem.nFormat = a_oCursor.getInt(3);
		oDbFileItem.nDateAdded = a_oCursor.getLong(4);
		oDbFileItem.nDateModified = a_oCursor.getLong(5);							
		oDbFileItem.strMimeType = a_oCursor.getString(6);
		oDbFileItem.strTitle = a_oCursor.getString(7);
		oDbFileItem.strBucketID = a_oCursor.getString(8);
		oDbFileItem.strBucketDisplayName = a_oCursor.getString(9);
		oDbFileItem.nMediaType = a_oCursor.getInt(10);
		return oDbFileItem;
	}
	
	private static String RemoveSlush(String a_strString){
		String strString = a_strString;
		int nLength = strString.length()-1;
		if(strString.lastIndexOf("/") == nLength){
			return strString.substring(0, nLength);
		}else
			return strString;
	}
}
